﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Xml.Linq;
using HRProject.Code.DTO;
using OfficeOpenXml;
using System.IO;
using System.Security.Policy;
using System.Xml;
using System.Text;
using HRProject.Code.Common;

namespace HRProject.Code.FAO
{
    public class WorktimeSheetExporter : BaseExcelExporter
    {
        private string templateFile;
        private string outputFile;

        public WorktimeSheetExporter(string templateFile, string outputFile)
        {
            this.templateFile = templateFile;
            this.outputFile = outputFile;
        }

        public MemoryStream Export(BLMonthlyWorktimeSheet worktimeSheet)
        {
            File.Copy(templateFile, outputFile);
            excelPackage = new ExcelPackage(new FileInfo(outputFile));
            xlWorkSheet = (ExcelWorksheet)excelPackage.Workbook.Worksheets[1];
            
            int totalDays = DateTime.DaysInMonth(worktimeSheet.Year,worktimeSheet.Month);

            int startRowIndex = 9;
            int serialColIndex = 1;
            int nameColIndex = 2;
            int startColIndex = 4;
            int sumColIndex = 35;

            int rowIndex = startRowIndex;
            int serial = 1;
            bool firstAgent = true;
            
            xlWorkSheet.Cell(6,1).Value = "Tháng mm Năm yyyy".Replace("mm",Convert.ToString(worktimeSheet.Month)).Replace("yyyy",Convert.ToString(worktimeSheet.Year));
            foreach (BLMonthlyWorktime agentWorktime in worktimeSheet.AgentsWorktime)
            {
                if (firstAgent)
                {
                    firstAgent = false;
                }
                else
                {
                    xlWorkSheet.InsertRow(rowIndex);
                    xlWorkSheet.Cell(rowIndex, 1).Style = "text1";
                    xlWorkSheet.Cell(rowIndex, 2).Style = "text2";
                    xlWorkSheet.Cell(rowIndex, 3).Style = "text3";
                    xlWorkSheet.Cell(rowIndex, sumColIndex + 2).Style = "number2";
                }
                xlWorkSheet.Cell(rowIndex, serialColIndex).Value = Convert.ToString(serial++);
                xlWorkSheet.Cell(rowIndex, nameColIndex).Value = agentWorktime.AgentFullName;
                int colIndex = startColIndex;
                foreach (BLDailyWorktime dateItem in agentWorktime.DaysWorktime)
                {
                    if ((dateItem.Total >= 1) || (dateItem.LeaveMorning<2 && dateItem.LeaveAfternoon<2))
                    {
                        xlWorkSheet.Cell(rowIndex, colIndex).Style = "number1";
                    }
                    else
                    {
                        xlWorkSheet.Cell(rowIndex, colIndex).Style = "number3";
                    }
                    xlWorkSheet.Cell(rowIndex, colIndex++).Value = dateItem.Total.ToString("0.0");
                }
                for (int k = totalDays; k < 31; k++)
                {
                    xlWorkSheet.Cell(rowIndex,startColIndex+k).Style = "number1";
                }
                xlWorkSheet.Cell(rowIndex, sumColIndex).Formula = "SUMIF(D" + rowIndex + ":AH" + rowIndex + ",\">=0\")";
                xlWorkSheet.Cell(rowIndex, sumColIndex).Style = "number1";
                xlWorkSheet.Cell(rowIndex, sumColIndex + 1).Formula = "=IF(AI" + rowIndex + ">=28,1,0)";
                xlWorkSheet.Cell(rowIndex, sumColIndex + 1).Style = "number1";
                xlWorkSheet.Cell(rowIndex, sumColIndex + 2).Formula = "=AI" + rowIndex + "+AJ" + rowIndex;
                xlWorkSheet.Cell(rowIndex, sumColIndex + 2).Style = "number2";
                rowIndex++;
            }

            if (worktimeSheet.AgentsWorktime.Count == 0)
            {
                rowIndex++;
            }

            int daysInMonth = DateTime.DaysInMonth(worktimeSheet.Year, worktimeSheet.Month);
            for (int i = 0; i < daysInMonth; i++)
            {
                ExcelCell cell = xlWorkSheet.Cell(rowIndex,startColIndex+ i);
                cell.Formula = "=sum(" + ExcelCell.GetCellAddress(startRowIndex, startColIndex + i) + ":" + ExcelCell.GetCellAddress(rowIndex-1, startColIndex + i) + ")";
            }

            for (int i = 0; i < 3; i++)
            {
                ExcelCell cell = xlWorkSheet.Cell(rowIndex, sumColIndex + i);
                cell.Formula = "=sum(" + ExcelCell.GetCellAddress(startRowIndex, sumColIndex + i) + ":" + ExcelCell.GetCellAddress(rowIndex - 1, sumColIndex + i) + ")";
            }


            base.Export();

            FileStream fs = new FileStream(outputFile, FileMode.Open);
            byte[] buffer = new byte[300000];
            int count = fs.Read(buffer, 0, 300000);
            Console.Out.WriteLine("read: " + count);
            fs.Close();
            MemoryStream stream = new MemoryStream(buffer, 0, count);
            buffer = null;
            File.Delete(outputFile);
            return stream;
        }

        public MemoryStream ExportOverWorktime(BLMonthlyWorktimeSheet worktimeSheet)
        {
            File.Copy(templateFile, outputFile);
            excelPackage = new ExcelPackage(new FileInfo(outputFile));
            xlWorkSheet = (ExcelWorksheet)excelPackage.Workbook.Worksheets[1];

            int totalDays = DateTime.DaysInMonth(worktimeSheet.Year, worktimeSheet.Month);

            int startRowIndex = 9;
            int serialColIndex = 1;
            int nameColIndex = 2;
            int startColIndex = 4;
            int sumColIndex = 35;

            int rowIndex = startRowIndex;
            int serial = 1;
            bool firstAgent = true;

            xlWorkSheet.Cell(6, 1).Value = "Tháng mm Năm yyyy".Replace("mm", Convert.ToString(worktimeSheet.Month)).Replace("yyyy", Convert.ToString(worktimeSheet.Year));
            foreach (BLMonthlyWorktime agentWorktime in worktimeSheet.AgentsWorktime)
            {
                if (firstAgent)
                {
                    firstAgent = false;
                }
                else
                {
                    xlWorkSheet.InsertRow(rowIndex);
                    xlWorkSheet.Cell(rowIndex, serialColIndex).Style = "c4";
                    xlWorkSheet.Cell(rowIndex, nameColIndex).Style = "c20";
                    xlWorkSheet.Cell(rowIndex, nameColIndex+1).Style = "c21";
                    xlWorkSheet.Cell(rowIndex, sumColIndex).Style = "c3";
                }
                xlWorkSheet.Cell(rowIndex, serialColIndex).Value = Convert.ToString(serial++);
                xlWorkSheet.Cell(rowIndex, nameColIndex).Value = agentWorktime.AgentFullName;
                int colIndex = startColIndex;
                foreach (BLDailyWorktime dateItem in agentWorktime.DaysWorktime)
                {
                    xlWorkSheet.Cell(rowIndex, colIndex).Style = "c1";
                    xlWorkSheet.Cell(rowIndex, colIndex++).Value = dateItem.TotalOver.ToString("0.0");
                }
                for (int k = totalDays; k < 31; k++)
                {
                    xlWorkSheet.Cell(rowIndex, startColIndex + k).Style = "number1";
                }
                xlWorkSheet.Cell(rowIndex, sumColIndex).Formula = "SUMIF(D" + rowIndex + ":AH" + rowIndex + ",\">=0\")";
                xlWorkSheet.Cell(rowIndex, sumColIndex).Style = "c3";
                rowIndex++;
            }

            if (worktimeSheet.AgentsWorktime.Count == 0)
            {
                rowIndex++;
            }

            int daysInMonth = DateTime.DaysInMonth(worktimeSheet.Year, worktimeSheet.Month);
            for (int i = 0; i < daysInMonth; i++)
            {
                ExcelCell cell = xlWorkSheet.Cell(rowIndex, startColIndex + i);
                cell.Formula = "=sum(" + ExcelCell.GetCellAddress(startRowIndex, startColIndex + i) + ":" + ExcelCell.GetCellAddress(rowIndex - 1, startColIndex + i) + ")";
            }
            ExcelCell cell2 = xlWorkSheet.Cell(rowIndex, sumColIndex);
            cell2.Formula = "=sum(" + ExcelCell.GetCellAddress(startRowIndex, sumColIndex) + ":" + ExcelCell.GetCellAddress(rowIndex - 1, sumColIndex) + ")";

            base.Export();

            FileStream fs = new FileStream(outputFile, FileMode.Open);
            byte[] buffer = new byte[300000];
            int count = fs.Read(buffer, 0, 300000);
            Console.Out.WriteLine("read: " + count);
            fs.Close();
            MemoryStream stream = new MemoryStream(buffer, 0, count);
            buffer = null;
            File.Delete(outputFile);
            return stream;
        }

        public MemoryStream Export(BLDailyWorktimeSheet worktimeSheet)
        {
            File.Copy(templateFile, outputFile);
            excelPackage = new ExcelPackage(new FileInfo(outputFile));
            xlWorkSheet = (ExcelWorksheet)excelPackage.Workbook.Worksheets[1];

            int startRowIndex = 9;
            int serialColIndex = 1;
            int nameColIndex = 2;
            int morningLeaveHasApp = 4;
            int morningLeaveNoApp = 5;
            int afternoonLeaveHasApp = 6;
            int afternoonLeaveNoApp = 7;

            int rowIndex = startRowIndex;
            int serial = 1;
            bool firstAgent = true;

            xlWorkSheet.Cell(6, 1).Value = "Ngày dd Tháng mm Năm yyyy"
                .Replace("dd", Convert.ToString(worktimeSheet.Date.Day))
                .Replace("mm", Convert.ToString(worktimeSheet.Date.Month))
                .Replace("yyyy", Convert.ToString(worktimeSheet.Date.Year));
            string tick = char.ConvertFromUtf32(252);
            foreach (BLDailyWorktime agentWorktime in worktimeSheet.AgentWorktimes)
            {
                if (firstAgent)
                {
                    firstAgent = false;
                }
                else
                {
                    xlWorkSheet.InsertRow(rowIndex);
                    xlWorkSheet.Cell(rowIndex, serialColIndex).Style = "c1";
                    xlWorkSheet.Cell(rowIndex, nameColIndex).Style = "c20";
                    xlWorkSheet.Cell(rowIndex, nameColIndex+1).Style = "c21";
                    xlWorkSheet.Cell(rowIndex, morningLeaveHasApp).Style = "c3";
                    xlWorkSheet.Cell(rowIndex, morningLeaveNoApp).Style = "c3";
                    xlWorkSheet.Cell(rowIndex, afternoonLeaveHasApp).Style = "c3";
                    xlWorkSheet.Cell(rowIndex, afternoonLeaveNoApp).Style = "c4";
                }
                xlWorkSheet.Cell(rowIndex, serialColIndex).Value = Convert.ToString(serial++);
                xlWorkSheet.Cell(rowIndex, nameColIndex).Value = agentWorktime.AgentFullName;
                xlWorkSheet.Cell(rowIndex, morningLeaveHasApp).Value = (agentWorktime.LeaveMorning == 1 ? tick : "");
                xlWorkSheet.Cell(rowIndex, morningLeaveNoApp).Value = (agentWorktime.LeaveMorning == 2 ? tick : "");
                xlWorkSheet.Cell(rowIndex, afternoonLeaveHasApp).Value = (agentWorktime.LeaveAfternoon == 1 ? tick : "");
                xlWorkSheet.Cell(rowIndex, afternoonLeaveNoApp).Value = (agentWorktime.LeaveAfternoon == 2 ? tick : "");
                rowIndex++;
            }


            base.Export();

            FileStream fs = new FileStream(outputFile, FileMode.Open);
            byte[] buffer = new byte[300000];
            int count = fs.Read(buffer, 0, 300000);
            Console.Out.WriteLine("read: " + count);
            fs.Close();
            MemoryStream stream = new MemoryStream(buffer, 0, count);
            buffer = null;
            File.Delete(outputFile);
            return stream;
        }

        public MemoryStream ExportOverWorktime(BLDailyWorktimeSheet worktimeSheet)
        {
            File.Copy(templateFile, outputFile);
            excelPackage = new ExcelPackage(new FileInfo(outputFile));
            xlWorkSheet = (ExcelWorksheet)excelPackage.Workbook.Worksheets[1];

            int startRowIndex = 8;
            int serialColIndex = 1;
            int nameColIndex = 2;
            int startTimeColIndex = 4;
            int endTimeColIndex = 5;
            int totalTimeColIndex = 6;

            int rowIndex = startRowIndex;
            int serial = 1;
            bool firstAgent = true;

            xlWorkSheet.Cell(6, 1).Value = "Ngày dd Tháng mm Năm yyyy"
                .Replace("dd", Convert.ToString(worktimeSheet.Date.Day))
                .Replace("mm", Convert.ToString(worktimeSheet.Date.Month))
                .Replace("yyyy", Convert.ToString(worktimeSheet.Date.Year));
            string tick = char.ConvertFromUtf32(252);
            foreach (BLDailyWorktime agentWorktime in worktimeSheet.AgentWorktimes)
            {
                if (firstAgent)
                {
                    firstAgent = false;
                }
                else
                {
                    xlWorkSheet.InsertRow(rowIndex);
                    xlWorkSheet.Cell(rowIndex, serialColIndex).Style = "c1";
                    xlWorkSheet.Cell(rowIndex, nameColIndex).Style = "c20";
                    xlWorkSheet.Cell(rowIndex, nameColIndex + 1).Style = "c21";
                    xlWorkSheet.Cell(rowIndex, startTimeColIndex).Style = "c3";
                    xlWorkSheet.Cell(rowIndex, endTimeColIndex).Style = "c3";
                    xlWorkSheet.Cell(rowIndex, totalTimeColIndex).Style = "c4";
                }
                xlWorkSheet.Cell(rowIndex, serialColIndex).Value = Convert.ToString(serial++);
                xlWorkSheet.Cell(rowIndex, nameColIndex).Value = agentWorktime.AgentFullName;

                string sStartHour = string.Empty;
                string sStartMin = string.Empty;
                string sEndHour = string.Empty;
                string sEndMin = string.Empty;
                string sTotal = string.Empty;
                string endDate = agentWorktime.Date.ToString(Constant.DateFormat);
                if (agentWorktime.OverWorktimes.Count > 0)
                {
                    sStartHour = agentWorktime.OverWorktimes[0].StartTime.Substring(0, 2);
                    sStartMin = agentWorktime.OverWorktimes[0].StartTime.Substring(3, 2);
                    sEndHour = agentWorktime.OverWorktimes[0].EndTime.Substring(0, 2);
                    sEndMin = agentWorktime.OverWorktimes[0].EndTime.Substring(3, 2);
                    sTotal = "00:00";
                    int startHour, startMin, endHour, endMin;
                    double total;
                    if (int.TryParse(sStartHour, out startHour)
                        && int.TryParse(sStartMin, out startMin)
                        && int.TryParse(sEndHour, out endHour)
                        && int.TryParse(sEndMin, out endMin))
                    {
                        if (endHour >= 24)
                        {
                            endDate = agentWorktime.Date.AddDays(1).ToString(Constant.DateFormat);
                            sEndHour = (endHour - 24).ToString("00");
                        }
                        else
                        {
                            endDate = agentWorktime.Date.ToString(Constant.DateFormat);
                        }
                        total = (endHour * 60 + endMin) - (startHour * 60 + startMin);
                        sTotal = Math.Floor(total / 60D).ToString("00") + ":" + (total % 60).ToString("00");
                    }
                }
                xlWorkSheet.Cell(rowIndex, startTimeColIndex).Value = (string.IsNullOrEmpty(sStartHour) ? "" : sStartHour + ":" + sStartMin);
                xlWorkSheet.Cell(rowIndex, endTimeColIndex).Value = (string.IsNullOrEmpty(sEndHour) ? "" : sEndHour + ":" + sEndMin);
                xlWorkSheet.Cell(rowIndex, totalTimeColIndex).Value = sTotal;
                rowIndex++;
            }


            base.Export();

            FileStream fs = new FileStream(outputFile, FileMode.Open);
            byte[] buffer = new byte[300000];
            int count = fs.Read(buffer, 0, 300000);
            Console.Out.WriteLine("read: " + count);
            fs.Close();
            MemoryStream stream = new MemoryStream(buffer, 0, count);
            buffer = null;
            File.Delete(outputFile);
            return stream;
        }
    }
}
